package db;

import db.DbConnection;
import java.nio.channels.NetworkChannel;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.LinkedList;
import java.util.List;

import com.mysql.jdbc.Statement;
import article.*;

public class News {

	Connection connection=null;
	java.sql.Statement statement=null;
	
	String sql="";
	
	ArticleInfo articleInfo;
	
	public News() {
		// TODO Auto-generated constructor stub
		articleInfo=new ArticleInfo();
		
		DbConnection db=new DbConnection();
		this.connection=db.getCon();
		
		try {
			this.statement=connection.createStatement();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	//获取文章详情
	public ArticleInfo GetNews(int id) 
	{
		//获取文章内容信息
		sql="select * from article where id="+id;
		
		try {
			ResultSet resultSet=this.statement.executeQuery(sql);
			while(resultSet.next()) {
				articleInfo.setId(resultSet.getInt("id"));
				articleInfo.setTitle(resultSet.getString("title"));
				articleInfo.setContent(resultSet.getString("content"));
				articleInfo.setAuthor(resultSet.getString("author"));
				articleInfo.setTypeId(resultSet.getInt("typeid"));
				articleInfo.setIsstime(resultSet.getString("isstime"));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		//获取评论内容
		sql="select * from comment where newsid="+id+"order by isstime desc";
		
		
		try {
			ResultSet resultSet=statement.executeQuery(sql);
			while(resultSet.next()) {
				articleInfo.comment.setId(resultSet.getInt("id"));
				articleInfo.comment.setArtid(resultSet.getInt("artid"));
				articleInfo.comment.setAuthor(resultSet.getString("author"));
				articleInfo.comment.setContent(resultSet.getString("content"));
				articleInfo.comment.setIsstime(resultSet.getString("isstime"));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		//获取文章类型
		sql="select * from types where id="+articleInfo.getTypeId();
		
		try {
			ResultSet resultSet=statement.executeQuery(sql);
			while(resultSet.next()) {
				articleInfo.types.setId(resultSet.getInt("id"));
				articleInfo.types.setName(resultSet.getString("name"));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		
		return articleInfo;
	}
	
	//添加文章
	public boolean AddNews(int typeId,String title,String content,String author)
	{
		boolean check=false;
		
		Date date=new Date();
		String isstime=date.toString();
		
		//添加文章内容
		String sql="insert into article(\"title\",\"content\",\"author\",\"typeId\",\"isstime\") "
				+ "values(\""+title+"\",\""+content+"\",\""+author+"\",\""+typeId+"\",\""+isstime+"\")";
		try {
			check=statement.execute(sql);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return check;
	}
	
	//删除文章
	public boolean DeleteNews(int id)
	{
		boolean check=false;
		
		String sql="delete from article where id="+id;
		
		try {
			check=statement.execute(sql);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return check;
	}
	
	//更新文章
	public boolean UpdateNews(int typeId,String title,String content,String author)
	{
		boolean check=false;
		
		String sql="update article set typeId="+typeId+" and title=\""+title+"\" and content=\""+content+"\" and author=\""+author+"\"";
		
		try {
			check=statement.execute(sql);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return check;
	}
	
	//分页获取文章内容
	public List<ArticleInfo> ListNews(int page,int limits,String type)
	{
		String sql;
		
		if(type==null||type.equals("")) {
			sql="select * from news where id>"+(page-1)*limits+" order by id desc limit "+limits;
		}else{
			sql="select * from news where id>"+(page-1)*limits+"and typeId=(select id from types where name=\""+type+"\") order by id desc limit "+limits;
		}
		
		List<ArticleInfo> articleInfos=new LinkedList<ArticleInfo>();
		
		try {
			ResultSet resultSet=statement.executeQuery(sql);
			while(resultSet.next()) {
				ArticleInfo arts=new ArticleInfo();
				
				arts.setId(resultSet.getInt("id"));
				arts.setTitle(resultSet.getString("title"));
				arts.setContent(resultSet.getString("content"));
				arts.setAuthor(resultSet.getString("author"));
				arts.setTypeId(resultSet.getInt("typeid"));
				arts.setIsstime(resultSet.getString("isstime"));
				
				articleInfos.add(arts);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return articleInfos;
	}
}
